package linuxbox.news.logic;

import java.util.*;

import org.apache.log4j.Logger;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import linuxbox.BoxStatic;
import linuxbox.util.*;

public class BoxNews2 {
    // define the View Types
    public final static String VT_ALL = "ALL";
    public final static String VT_CURRENT = "CURRENT";
    public final static String VT_EXPIRED = "EXPIRED";

    public final static String ORDER_BY_SUBTIME = "subtime";
    
    protected static Logger logger = Logger.getLogger(
            BoxNews2.class.getName());

    public static BoxNews2 staticInstance = null;
    
    public static BoxNews2 getInstance() {
        if (staticInstance == null) {
            staticInstance = new BoxNews2();
        }
        
        return staticInstance;
    }

    private BoxNews2() {
    }

    public BoxNewsEntry get(int key) {
        BoxNewsEntry entry = null;
        Transaction tx = null;
        Session session = HibernateUtil.getInstance().getCurrentSession();
        try {
            tx = session.beginTransaction();
            List newsList = session.createQuery("select news from " +
            		"BoxNewsEntry as news where id=" + key).list();
            if (newsList.size() > 0) {
                entry = (BoxNewsEntry)newsList.get(0);
            }
            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null && tx.isActive()) {
                try {
                    // Second try catch as the rollback could fail as well
                    tx.rollback();
                } catch (HibernateException e1) {
                    logger.debug("Error rolling back transaction");
                }
                // throw again the first exception
                throw e;
            }
        }
        
        return entry;
    }

    public void add(BoxNewsEntry entry) {
        Transaction tx = null;
        Session session = HibernateUtil.getInstance().getCurrentSession();
        try {
            tx = session.beginTransaction();
            session.save(entry);
            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null && tx.isActive()) {
                try {
                    // Second try catch as the rollback could fail as well
                    tx.rollback();
                } catch (HibernateException e1) {
                    logger.debug("Error rolling back transaction");
                }
                // throw again the first exception
                throw e;
            }
        }
    }

    public boolean remove(int key) {
        boolean ret = false;
        Session session = HibernateUtil.getInstance().getCurrentSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();
            //String hql = "delete from BoxNewsEntry where id=" + key;
            //Query qry = session.createQuery(hql);
            //int row = qry.executeUpdate();
            //if (row > 0) ret = true;
            Object obj = session.get(BoxNewsEntry.class, key);
            session.delete(obj);
            tx.commit();
        } catch (Exception e) {
            logger.error("Failed to delete BoxNewsEntry id = " + key
                    + "\n" + e.getMessage());
        }
        return ret;
    }

    public boolean update(BoxNewsEntry entry) {
        Transaction tx = null;
        Session session = HibernateUtil.getInstance().getCurrentSession();
        try {
            tx = session.beginTransaction();
            session.update(entry);
            tx.commit();
        } catch (RuntimeException e) {
            if (tx != null && tx.isActive()) {
                try {
                    // Second try catch as the rollback could fail as well
                    tx.rollback();
                } catch (HibernateException e1) {
                    logger.debug("Error rolling back transaction");
                }
                // throw again the first exception
                throw e;
            }
        }
        
        return true;
    }

    public int getTotalPages(int entriesPerPage, String VIEW_TYPE) {
        int pages = 1;
        try {
            StringBuffer sql = new StringBuffer();
            BoxStatic.dateTimeFormatter.applyPattern(
                    BoxStatic.POSTGRES_DATE_FORMAT);

            if (VT_CURRENT.compareTo(VIEW_TYPE) == 0) {
                sql.append(" where  expTime >= '");
                String strDate = BoxStatic.dateTimeFormatter
                        .format(new java.util.Date());
                sql.append(strDate);
                sql.append("' or expTime is null ");
            } else if (VT_EXPIRED.compareTo(VIEW_TYPE) == 0) {
                sql.append(" where  expTime < '");
                String strDate = BoxStatic.dateTimeFormatter
                        .format(new java.util.Date());
                sql.append(strDate);
                sql.append("' ");
            }

            int count = 0;
            Session session = HibernateUtil.getInstance().getCurrentSession();
            Transaction tx = null;

            try {
                tx = session.beginTransaction();

                String hql = "select count(news.id) from BoxNewsEntry as news "
                    + sql;
                logger.debug(sql);
                Query qry = session.createQuery(hql);
                count = ((Long)qry.uniqueResult()).intValue();
                tx.commit();
            } catch (Exception e) {
                logger.error("getBySql fail: " + e.getMessage());
            }

            pages = ((count - 1) / entriesPerPage) + 1;
        } catch (Exception e) {
            e.printStackTrace();
            // TODO: Log
        }
        return pages;
    }

    public List<BoxNewsEntry> getNews(String VIEW_TYPE, String SORTBY, 
            boolean sortAsc, int from, int limit) {
        if (VT_CURRENT.compareTo(VIEW_TYPE) == 0) {
            return getCurrentNews(SORTBY, sortAsc, from, limit);
        } else if (VT_EXPIRED.compareTo(VIEW_TYPE) == 0) {
            return getExpiredNews(SORTBY, sortAsc, from, limit);
        } else
            return getAllNews(SORTBY, sortAsc, from, limit);
    }

    public List<BoxNewsEntry> getAllNews(String SORTBY, boolean sortAsc, int from,
            int limit) {
        // TODO: check SORYBY is a correct column name
        StringBuffer sql = new StringBuffer();
        sql.append(" order by ");
        sql.append(SORTBY);
        if (sortAsc)
            sql.append(" asc ");
        else
            sql.append(" desc ");

        return getBySql(sql.toString(), from, limit);
    }

    public List<BoxNewsEntry> getCurrentNews(String SORTBY, boolean sortAsc, int from,
            int limit) {
        BoxStatic.dateTimeFormatter
                .applyPattern(BoxStatic.POSTGRES_DATE_FORMAT);
        String strDate = BoxStatic.dateTimeFormatter
                .format(new java.util.Date());
        StringBuffer sql = new StringBuffer();
        sql.append(" where  expTime >= '");
        sql.append(strDate);
        sql.append("' or expTime is null order by ");
        sql.append(SORTBY);
        if (sortAsc)
            sql.append(" asc ");
        else
            sql.append(" desc ");

        return getBySql(sql.toString(), from, limit);
    }

    public List<BoxNewsEntry> getExpiredNews(String SORTBY, boolean sortAsc, int from,
            int limit) {
        BoxStatic.dateTimeFormatter
                .applyPattern(BoxStatic.POSTGRES_DATE_FORMAT);
        String strDate = BoxStatic.dateTimeFormatter
                .format(new java.util.Date());
        StringBuffer sql = new StringBuffer();
        sql.append(" where  expTime < '");
        sql.append(strDate);
        sql.append("' order by ");
        sql.append(SORTBY);
        if (sortAsc)
            sql.append(" asc ");
        else
            sql.append(" desc ");

        return getBySql(sql.toString(), from, limit);
    }

    protected List<BoxNewsEntry> getBySql(String sql, int offset, int count) {
        List<BoxNewsEntry> v = new ArrayList<BoxNewsEntry>();
        Session session = HibernateUtil.getInstance().getCurrentSession();
        Transaction tx = null;
        try {
            tx = session.beginTransaction();

            String hql = "select news from BoxNewsEntry as news " + sql; 
            Query qry = session.createQuery(hql);
            qry.setFirstResult(offset);
            qry.setMaxResults(count);
            List newsList = qry.list();
            for (Iterator iter = newsList.iterator(); iter.hasNext(); ) {
                BoxNewsEntry entry = (BoxNewsEntry)iter.next();
                v.add(entry);
            }
            
            tx.commit();
        } catch (Exception e) {
            logger.error("getBySql fail: " + e.getMessage());
        }

        return v;
    }
}
